﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using MySql.Data.MySqlClient;


public class CardapioDAO
{
    MySqlConnection connection = Database.getInstance.getConnection();

    public CardapioDAO()
    {

    }

    public List<CardapioDTO> getAll()
    {
        try
        {
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "SELECT * FROM cardapio";
            connection.Open();

            List<CardapioDTO> Cardapio_List = new List<CardapioDTO>();

            reader = command.ExecuteReader();
            while (reader.Read())
            {
                CardapioDTO cardapio = new CardapioDTO();

                cardapio.Idcardapio = Convert.ToInt32(reader["idcardapio"]);  
                cardapio.Descricao = (string)(reader["descricao"]);

                Cardapio_List.Add(cardapio);
            }
            connection.Close();
            return Cardapio_List;
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }

    public List<CardapioFotoDTO> getAllCardapioFoto()
    {
        try
        {
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "SELECT * FROM cardapio INNER JOIN foto_has_cardapio ON foto_has_cardapio.cardapio_idcardapio = cardapio.idcardapio INNER JOIN foto ON foto.idfoto = foto_has_cardapio.foto_idfoto";
            connection.Open();

            List<CardapioFotoDTO> Cardapio_List = new List<CardapioFotoDTO>();

            reader = command.ExecuteReader();
            while (reader.Read())
            {
                CardapioFotoDTO cardapioFoto = new CardapioFotoDTO();

                cardapioFoto.Idcardapio = Convert.ToInt32(reader["idcardapio"]);             
                cardapioFoto.Idfoto = Convert.ToInt32(reader["idfoto"]);

                cardapioFoto.Descricao = (string)(reader["descricao"]);
                cardapioFoto.Observacao = (string)(reader["observacao"]);
                cardapioFoto.Caminho = (string)(reader["caminho"]);

                Cardapio_List.Add(cardapioFoto);
            }
            connection.Close();
            return Cardapio_List;
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }

    public bool insere(CardapioDTO cardapio)
    {
        try
        {
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "INSERT INTO cardapio (descricao) VALUES (@descricao)";
            connection.Open();

            command.Parameters.AddWithValue("@descricao", cardapio.Descricao);
            command.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (SqlException ex)
        {
            return false;
        }
    }

    public bool atualiza(CardapioDTO cardapio)
    {

        try
        {

            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "UPDATE cardapio SET descricao=@descricao WHERE idcardapio=@idcardapio";
            connection.Open();

            command.Parameters.AddWithValue("@descricao", cardapio.Descricao);
            command.Parameters.AddWithValue("@idcardapio", cardapio.Idcardapio);

            command.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }

    }


}
